﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Xml;
using ClosedXML.Excel;

// from: http://closedxml.codeplex.com/
// uses ClosedXML.dll and Verweis
namespace NSDecisionTree.Tools
{
    public class ExcelFileHandling
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="Captions"></param>
        /// <param name="RowElements"></param>
        public static void LoadFromExcel(string filename, out List<string> Captions, out List<List<string>> RowElements)
        {
            bool error = true;
            List<string> captions = new List<string>();
            List<List<string>> rowContent = new List<List<string>>();

            try
            {
                string tempFile = Path.GetTempFileName();
                File.Copy(filename, tempFile,true);
                ClosedXML.Excel.XLWorkbook wb = new XLWorkbook(tempFile);
                var worksheet = wb.Worksheets.ElementAt(0);
                IXLCell first = worksheet.FirstCellUsed();
                IXLCell last = worksheet.LastCellUsed();
                var worksheetrange = worksheet.Range(first.Address, last.Address);
                var table = worksheetrange.AsTable();
                var categoryRow = worksheet.FirstRowUsed().RowUsed();

                foreach (IXLCell cell in categoryRow.Cells())
                {
                    captions.Add(cell.Value.ToString());
                }

                for (int i = 1; i < table.RowCount(); i++)
                {
                    IXLTableRow row = table.Row(i);
                    List<string> ls = new List<string>();

                    for (int j = 1; j <= row.CellCount(); j++)
                    {
                        string temp = row.Cell(j).Value.ToString();
                        if (temp == String.Empty)
                            throw new Exception("Value is Empty");

                        ls.Add(temp);
                    }
                    rowContent.Add(ls);
                }


                File.Delete(tempFile);
                error = false;
            }
            catch (Exception ex)
            {
                throw new Exception("Could not open File");
            }

            if (error)
            {
                captions = null;
                rowContent = null;
            }

            Captions = captions;
            RowElements = rowContent;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filename"></param>
        public static void SaveToExcel(DataTable dt, string filename)
        {
            ClosedXML.Excel.XLWorkbook wb = new XLWorkbook();
            dt.TableName = "Table1";
            wb.Worksheets.Add(dt);
            wb.SaveAs(filename);
        }
    }   
}
